var express = require('express');
var router = express.Router();
var conn = require('../conn');

/* GET home page. */
router.get('/',  function(req, res, next) {
	conn.query('select * from catalog', function(err, rows, fields) {
		req.catalog = rows;
		next();
		})
},function(req, res, next) {
	var sql='select count(1) as c from articles';
	conn.query(sql, function(err, rows, fields) {
		req.totolpage = rows[0].c;
		next();
	})
},function(req, res, next) {
	var page = req.query.page;
	var pagenum = '10';
	var totolpage =Math.ceil(req.totolpage/pagenum) 
	catalog = req.catalog;
	sql = 'select *,articles.id as aid from articles join catalog on articles.cata_id=catalog.id order by aid desc limit '
	if (page == undefined) {
		page=1
	}
	sql = sql+(page-1)*pagenum+','+pagenum;
	conn.query(sql, function(err, rows, fields) {
		if (err) throw err;
		item = rows;
  			res.render('index', { title: '机友汇(jiyouh.com)' ,item:item,cata_id:0,totolpage:totolpage,currentpage:page,catalog:catalog});
	})
});
router.get('/news', function(req, res, next) {
	var sql='select count(1) as c from articles';
	conn.query(sql, function(err, rows, fields) {
		req.totolpage = rows[0].c;
		next();
	});
},function(req, res, next) {
	conn.query('select * from catalog', function(err, rows, fields) {
		req.catalog = rows;
		next();
		})
}, function(req, res, next) {
	var page = req.query.page;
	var pagenum = '10';
	var totolpage =Math.ceil(req.totolpage/pagenum); 
	var catalog = req.catalog;
	
	if (page == undefined) {
		page=1
		var sql = 'select *,articles.id as aid from articles join catalog on articles.cata_id = catalog.id where cata_id = '+req.query.id+' limit '
	}else{
		var sql = 'select *,articles.id as aid from articles join catalog on articles.cata_id = catalog.id where cata_id = catalog.id limit '
	}
	sql = sql+(page-1)*pagenum+','+pagenum;
	console.log(sql)
	conn.query(sql, function(err, rows, fields) {
		if (err) throw err;
		item = rows;
	  		res.render('list', { title: '机友汇(jiyouh.com)' ,item:item,cata_id:req.query.id,totolpage:totolpage,currentpage:page,catalog:catalog});
	})
});
router.get('/article', function(req, res, next) {
	conn.query('select * from catalog', function(err, rows, fields) {
		if (err) throw err;
		catalog = rows;
		conn.query('select * from articles where id = '+req.query.id, function(err, rows, fields) {
			if (err) throw err;
			item = rows;
		  		res.render('article', { title: '机友汇(jiyouh.com)' ,article:rows[0],catalog:catalog,cata_id:rows[0].cata_id});
		})
	})																				
});

module.exports = router;
